conn / as sysdba

drop user music cascade;

CREATE USER music IDENTIFIED BY music;
GRANT connect TO music;
GRANT connect,resource TO music;

conn music/music

-------------회원 테이블
drop table members;
create table members(
	m_id varchar2(50) primary key,   ------아이디
	m_pw varchar2(50),		----비번
	m_nickname varchar2(50),	----닉네임
	m_email varchar2(100),		----이메일
	m_ssn1 number,			----주민번호 앞자리
	m_ssn2 number,			----주민번호 뒷자리
	m_addres varchar2(100)		----주소
);
insert into members values('music1','music1','music1','music1@naver.com',910227,1123122,'서울');
insert into members values('music2','music2','music2','music2@naver.com',880721,1767762,'부산');
insert into members values('music3','music3','music3','music3@naver.com',840526,1345532,'경기도');
insert into members values('music4','music4','music4','music4@naver.com',900806,1876542,'대구');
insert into members values('music5','music5','music5','music5@naver.com',930529,1345672,'강원도');

-------------이벤트 게시판 시퀀스
drop sequence seq_event;
create sequence seq_event start with 1 increment by 1;
----------이벤트 테이블
drop table eboard;
create table eboard(
	eb_no number primary key,	----시퀀스
	eb_title varchar2(100),		----글 제목
	eb_context varchar2(2000),	---- 내용
	eb_count number			----조회수
);

insert into eboard values(seq_event.nextval,'이달의 상품','1개를 다운 받으면 1개를 덤으로 드립니다.',8);
insert into eboard values(seq_event.nextval,'패키지 상품','패키지로 구매시 1000원이 할인됩니다.',8);



----------가수 정보 테이블 시퀀스
drop sequence seq_singer;
create sequence seq_singer start with 1 increment by 1;
--------- 가수 정보 테이블
drop table singer;
create table singer(
	s_no number primary key,	---- 시퀀스
	s_singer varchar2(50),	----- 가수 이름
	s_gender varchar2(10),		---- 가수 성별
	s_members varchar2(10)		---- 그룹
);

------------노래 시퀀스
drop sequence seq_music;
create sequence seq_music start with 1 increment by 1;
------------노래 테이블
drop table music;
create table music(
	ms_no number primary key,	---- 노래번호
	ms_title varchar2(50),		---- 노래 제목
	ms_rd number,    			---- 발매일
	ms_featuring varchar2(20),	---- 피처링
	ms_genre varchar2(20),		---- 장르
	ms_album varchar2(50),		---- 앨범
	ms_s_no number references singer(s_no) ---- 가수정보 외래키
);


INSERT INTO singer VALUES (seq_singer.nextval,'소유X정기고','혼성','그룹');
 INSERT INTO music VALUES (seq_music.nextval,'썸','20140207','릴보이','알앤비/어반','썸',1); 
INSERT INTO singer VALUES (seq_singer.nextval,'Idina Menzel','여','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'Let it Go','20131205','없음','애니메이션','겨울왕국 OST(Frozen OST)',2); 
INSERT INTO singer VALUES (seq_singer.nextval,'효린','여','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'안녕','20140122','없음','드라마음악','별에서 온 그대 OST Part 4',3); 
INSERT INTO singer VALUES (seq_singer.nextval,'허각','남','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'오늘 같은 눈물이','20140205','없음','드라마음악','별에서 온 그대 OST Part 6',4); 
INSERT INTO singer VALUES (seq_singer.nextval,'성시경','남','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'너의 모든 순간','20140212','없음','드라마음악','별에서 온 그대 OST Part 7',5); 
INSERT INTO singer VALUES (seq_singer.nextval,'걸스데이(Girls Day)','여','그룹');
 INSERT INTO music VALUES (seq_music.nextval,'Something','20140103','없음','댄스','걸스데이 미니앨범',6); 
INSERT INTO singer VALUES (seq_singer.nextval,'AOA','여','그룹');
 INSERT INTO music VALUES (seq_music.nextval,'짧은 치마(Miniskirt)','20140116','없음','댄스','짧은 치마',7); 
INSERT INTO singer VALUES (seq_singer.nextval,'가인','여','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'Fxxk U','20140206','범키','댄스','Truth Or Dare',8); 
INSERT INTO singer VALUES (seq_singer.nextval,'S.M. THE BALLAD','혼성','그룹');
 INSERT INTO music VALUES (seq_music.nextval,'숨소리','20140210','없음','발라드','S.M. THE BALLAD Vol. 2',9); 
INSERT INTO singer VALUES (seq_singer.nextval,'에일리','여','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'노래가 늘었어','20140106','없음','발라드/락','노래가 늘었어',10); 
INSERT INTO singer VALUES (seq_singer.nextval,'스피카(SPICA)','여','그룹');
 INSERT INTO music VALUES (seq_music.nextval,'You Dont Love Me','20140127','없음','댄스','You Dont Love Me',11); 
INSERT INTO singer VALUES (seq_singer.nextval,'케이윌(K.will)/마마무(Mamamoo)','혼성','그룹');
 INSERT INTO music VALUES (seq_music.nextval,'썸남썸녀','20140211','휘성','댄스','썸남썸녀',12); 
INSERT INTO singer VALUES (seq_singer.nextval,'Kristen Bell','여','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'Do You Want To Build A Snowman?','20131202','없음','애니메이션','겨울왕국 OST(Frozen OST)',13); 
INSERT INTO singer VALUES (seq_singer.nextval,'가인','여','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'진실 혹은 대담','20140206','없음','댄스','Truth Or Dare',14); 
INSERT INTO singer VALUES (seq_singer.nextval,'린(LYn)','여','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'My Destiny','20131226','없음','드라마음악','별에서 온 그대 OST Part 1',15); 
INSERT INTO singer VALUES (seq_singer.nextval,'선미','여','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'보름달','20140217','Lena','댄스','Full Moon',16); 
INSERT INTO singer VALUES (seq_singer.nextval,'비(Rain)','남','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'LA SONG','20140102','없음','댄스','RAIN EFFECT',17); 
INSERT INTO singer VALUES (seq_singer.nextval,'홍대광','남','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'답이 없었어','20140122','없음','발라드','THE SILVER LINING 답이 없었어',18); 
INSERT INTO singer VALUES (seq_singer.nextval,'M.C. the Max','남','그룹');
 INSERT INTO music VALUES (seq_music.nextval,'그대가 분다','20140102','없음','발라드','UNVEILING (언베일링)',19); 
INSERT INTO singer VALUES (seq_singer.nextval,'케이윌(K.will)','남','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'별처럼','20140108','없음','드라마음악','별에서 온 그대 OST Part 2',20); 
INSERT INTO singer VALUES (seq_singer.nextval,'Gary','남','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'조금 이따 샤워해','20140115','Crush','랩/힙합','MR.GAE',21); 
INSERT INTO singer VALUES (seq_singer.nextval,'B1A4','남','그룹');
 INSERT INTO music VALUES (seq_music.nextval,'Lonely(없구나)','20140113','없음','댄스','Who Am I',22); 
INSERT INTO singer VALUES (seq_singer.nextval,'거미','여','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'내 생각날 거야','20140117','이시영','발라드','The Lyrics (더 리릭스) - No.4',23); 
INSERT INTO singer VALUES (seq_singer.nextval,'M 이민우','남','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'Taxi','20140206','ERIC','댄스','M+TEN (엠텐)',24); 
INSERT INTO singer VALUES (seq_singer.nextval,'아이유(IU)','여','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'금요일에 만나요','20131220','장이정','발라드','Modern Times - Epilogue',25); 
INSERT INTO singer VALUES (seq_singer.nextval,'Santino Fontana','여','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'Love Is An Open Door','20131202','없음','애니메이션','겨울왕국 OST(Frozen OST)',26); 
INSERT INTO singer VALUES (seq_singer.nextval,'브라운 아이드 소울','남','그룹');
 INSERT INTO music VALUES (seq_music.nextval,'Pass Me By','20140214','없음','알앤비/어반','Thank Your Soul - SIDE A',27); 
INSERT INTO singer VALUES (seq_singer.nextval,'레인보우 블랙','여','그룹');
 INSERT INTO music VALUES (seq_music.nextval,'Cha Cha','20140120','없음','댄스','Rainbow Blaxx Special Album (RB BLAXX)',28); 
INSERT INTO singer VALUES (seq_singer.nextval,'심은경','여','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'나성에 가면','20140128','없음','영화음악','수상한 그녀 OST',29); 
INSERT INTO singer VALUES (seq_singer.nextval,'임정희','여','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'꽃향기','20140207','없음','드라마음악','응급남녀 OST Part 2',30); 
INSERT INTO singer VALUES (seq_singer.nextval,'휘성','남','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'가슴 시린 이야기','20110315','용준형','알앤비/어반','가슴 시린 이야기',31); 
INSERT INTO singer VALUES (seq_singer.nextval,'레이디스 코드(LADIES CODE)','여','그룹');
 INSERT INTO music VALUES (seq_music.nextval,'So Wonderful','20140213','없음','댄스','So Wonderful',32); 
INSERT INTO singer VALUES (seq_singer.nextval,'김수현','남','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'너의 집 앞','20140219','없음','드라마음악','별에서 온 그대 OST Part 8',33); 
INSERT INTO singer VALUES (seq_singer.nextval,'윤하','여','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'별에서 온 그대','20140115','없음','드라마음악','별에서 온 그대 OST Part 3',34); 
INSERT INTO singer VALUES (seq_singer.nextval,'포미닛(4minute)','여','그룹');
 INSERT INTO music VALUES (seq_music.nextval,'살만찌고','20140120','없음','댄스','살만찌고',35); 
INSERT INTO singer VALUES (seq_singer.nextval,'Apink','여','그룹');
 INSERT INTO music VALUES (seq_music.nextval,'Good Morning Baby','20140113','없음','댄스','Good Morning Baby',36); 
INSERT INTO singer VALUES (seq_singer.nextval,'달샤벳(Dalshabet)','여','그룹');
 INSERT INTO music VALUES (seq_music.nextval,'B.B.B (Big Baby Baby)','20140108','없음','발라드','THE 7TH MINI ALBUM B.B.B',37); 
INSERT INTO singer VALUES (seq_singer.nextval,'유승우','남','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'입술이 밉다','20140210','없음','발라드','빠른 열아홉',38); 
INSERT INTO singer VALUES (seq_singer.nextval,'소녀시대','여','그룹');
 INSERT INTO music VALUES (seq_music.nextval,'Mr.Mr.','20140224','없음','댄스','The 4th Mini Album Mr.Mr.',39); 
INSERT INTO singer VALUES (seq_singer.nextval,'Kristen Bell','여','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'For The First Time In Forever','20131205','없음','애니메이션','겨울왕국 OST(Frozen OST)',40); 
INSERT INTO singer VALUES (seq_singer.nextval,'Vibe','남','그룹');
 INSERT INTO music VALUES (seq_music.nextval,'해운대','20140220','강민경','발라드','Ritardando',41); 
INSERT INTO singer VALUES (seq_singer.nextval,'심은경','여','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'하얀 나비','20140128','없음','영화음악','수상한 그녀 OST',42); 
INSERT INTO singer VALUES (seq_singer.nextval,'효린','여','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'Let It Go (겨울왕국 OST 효린 버전)','20140210','없음','애니메이션','Let It Go (겨울왕국 OST 효린 버전)',43); 
INSERT INTO singer VALUES (seq_singer.nextval,'비(Rain)','남','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'사랑해','20140207','없음','발라드','RAIN EFFECT - Special Edition',44); 
INSERT INTO singer VALUES (seq_singer.nextval,'B.A.P','남','그룹');
 INSERT INTO music VALUES (seq_music.nextval,'1004 (Angel)','20140203','없음','댄스','First Sensibility',45); 
INSERT INTO singer VALUES (seq_singer.nextval,'저스트(Just)','남','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'I Love You','20140129','없음','드라마음악','별에서 온 그대 OST Part 5',46); 
INSERT INTO singer VALUES (seq_singer.nextval,'소유','여','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'한번만','20140107','없음','드라마음악','기황후 OST Part 4',47); 
INSERT INTO singer VALUES (seq_singer.nextval,'휘성','남','솔로');
 INSERT INTO music VALUES (seq_music.nextval,'결혼까지 생각했어','20100826','없음','알앤비/어반','Realslow Is Back',48); 
INSERT INTO singer VALUES (seq_singer.nextval,'스텔라(Stellar)','여','그룹');
 INSERT INTO music VALUES (seq_music.nextval,'마리오네트','20140212','없음','댄스','마리오네트',49); 
INSERT INTO singer VALUES (seq_singer.nextval,'휘성/거미','혼성','그룹');
 INSERT INTO music VALUES (seq_music.nextval,'Special Love','20131129','없음','알앤비/어반','Special Love',50); 






------------가수 게시판 시퀀스
drop sequence seq_singerboard;
create sequence seq_singerboard start with 1 increment by 1;
------------가수 게시판 테이블
drop table sboard;
create table sboard(
	sb_no number primary key,	----시퀀스
	sb_title varchar2(100),		----글 제목
	sb_context varchar2(2000),		----글 내용
	sb_m_id varchar2(50) references members(m_id) on delete set null, ----작성자
	sb_s_no number references singer(s_no)	---- 가수테이블 외래키
);

insert into sboard values(seq_singerboard.nextval,'에이핑크 사랑해요 동림이가','노노노 뻥입니다.','music1',1);



------------- 카운터 테이블 시퀀스
drop sequence seq_mcount;
create sequence seq_mcount start with 1 increment by 1;
------------- 카운터 테이블
drop table mcount;
create table mcount(
	mc_no number primary key,	---- 구매 번호
	mc_dlcount number,		---- 구매 횟수
	mc_ms_no number references music(ms_no)	---- 노래 번호 외래키
);

insert into mcount values(seq_mcount.nextval,10,1);
insert into mcount values(seq_mcount.nextval,25,2);
insert into mcount values(seq_mcount.nextval,30,39);

------------- 구매 시퀀스
drop sequence seq_download;
create sequence seq_download start with 1 increment by 1;
------------- 구매 테이블
drop table download;
create table download(
	d_no number primary key,	---- 구매 시퀀스
	d_ms_no number references music(ms_no),	---- 노래 테이블 번호
	d_m_id varchar2(50) references members(m_id),	---- 회원 테이블 번호
	d_buyday date			---- 구매 날짜
);

insert into download values(seq_download.nextval,1,'music1',sysdate);




-- hadoop

drop table arealike;
---------- 지역별 선호도
create table arealike(
	a_singer varchar2(20),
	a_title varchar2(50),
	a_area varchar2(50)
);

drop table singergenderlike;
---------- 가수별 성별 선호도
create table singergenderlike(
	sgl_title varchar2(50),
	sgl_gender varchar2(10)
);

drop table areagendergenrelike;
---------- 지역별 성별 장르 선호도
create table areagendergenrelike(
	aggl_area varchar2(50),		---- 지역
	aggl_gender varchar2(10),	---- 장르
	aggl_genre varchar2(20)		---- 성별
);

drop table areagendersingerlike;
---------- 지역 성별 가수 선호도
create table areagendersingerlike(
	agsl_area varchar2(50),		---- 지역
	agsl_gender varchar2(10),	---- 성별
	agsl_singer varchar2(20)	---- 가수
);

drop table musiclikeage;
---------- 음악 나이별 선호도
create table musiclikeage(
	mla_singer varchar2(20),	---- 가수
	mla_title varchar2(50),		---- 제목
	mla_age number			---- 나이
);

drop table singerlikeage;
---------- 가수 나이별 선호도
create table singerlikeage(
	sla_singer varchar2(20),	---- 가수
	sla_age number			---- 나이
);

drop table sales;
---------- 발매량
create table sales(
	s_month number,			---- 발매 월
	s_album varchar2(50)		---- 앨범
);

drop table monthgenrelike;
create table monthgenrelike(
	mgl_genre varchar2(50),
	mgl_month number
);

commit;
